package intellitor.sqlparser.ddl.translater;

import intellitor.sqlparser.ddl.DDLTranslater;
import intellitor.sqlparser.ddl.TableBean;
import intellitor.sqlparser.ddl.exception.BaseException;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;



/**
 * 把数据定义语言翻译成ORACLE版的SQL语句
 * @author DevilSoul
 * @since 2015-10-28
 * @version 1.0.0
 */
public class DDLTrans2Oracle implements DDLTranslater{

	private static final Logger log = Logger.getLogger(DDLTrans2Oracle.class);
	private static final SimpleDateFormat CONST_NAME = new SimpleDateFormat("yyyyMMddHHmmssSSS");
	
	@Override
	public String translate(TableBean ddl) throws Exception {
		//构造ORACL的SQL语句
		StringBuilder sql = new StringBuilder();
		sql.append("--Created By The DevilSoul's Auto SQL Tool.\n\n");
		try {
			if(null == ddl || null == ddl.getTableList() || ddl.getTableList().isEmpty()){
				throw new BaseException("数据定义语言为空！");
			}
			for(TableBean tb : ddl.getTableList()){
				StringBuilder tmpSQL = new StringBuilder();
				StringBuilder tmpConstraints = new StringBuilder();
				StringBuilder tmpComments = new StringBuilder();
				StringBuilder tmpSeq = new StringBuilder();
				try {
					if(StringUtils.isBlank(tb.getTableNameEN())){
						throw new BaseException("表名为空！");
					}
					if(null == tb.getTableColumnsList() || tb.getTableColumnsList().isEmpty()){
						throw new BaseException("表的列为空！");
					}
					//新增操作，忽略备注后面的操作标注
					if("NEW".equals(tb.getTableOpType())){
						//序列
						if(StringUtils.isNotBlank(tb.getTableSeq())){
							tmpSeq.append("CREATE SEQUENCE ").append(tb.getTableSeq()).append("\n");
							tmpSeq.append("MINVALUE 1").append("\n");
							tmpSeq.append("MAXVALUE 9999999999").append("\n");
							tmpSeq.append("START WITH 50").append("\n");
							tmpSeq.append("INCREMENT BY 1").append("\n");
							tmpSeq.append("CACHE 30;").append("\n");
						}
						//注解
						tmpComments.append("COMMENT ON TABLE ").append(tb.getTableNameEN()).append(" IS '").append(tb.getTableNameCN()).append(preSpaceFormatter(tb.getTableDesc())).append("';\n");
						
						tmpSQL.append("CREATE TABLE ").append(tb.getTableNameEN()).append("(\n");
						//主键
						String pk = new String();
						//唯一键
						List<String> uk = new ArrayList<String>();
						//外键
						List<String> fk = new ArrayList<String>();
						//联合主键
						StringBuilder jpk = new StringBuilder();
						//联合唯一
						Map<String,List<String>> juk = new HashMap<String,List<String>>();
						for(String key : tb.getTableColumnsList()){
							if(StringUtils.isBlank(key)){
								continue;
							}
							String type = tb.getTableColumnTypeMap().get(key);
							if(StringUtils.isBlank(type)){
								continue;
							}
							String dfv = StringUtils.isBlank(tb.getTableColumnDefaultValueMap().get(key))?"":" DEFAULT "+tb.getTableColumnDefaultValueMap().get(key);
							tmpSQL.append("    ").append(key).append(" ").append(type).append(dfv).append(getNullOrNotStr(tb.getTableConstraintsMap().get(key))).append(",\n");
							
							String cst = tb.getTableConstraintsMap().get(key);
							if("PK".equals(cst)){
								if(StringUtils.isBlank(pk)){
									pk = key;
								}else{
									log.info("表中存在多个单一主键，忽略后续的主键");
									throw new BaseException(tb.getTableNameEN()+"表存在多个PK，请检查表定义！");
								}
							}else if("FK".equals(cst)){
							    fk.add(key);
							}else if("JPK".equals(cst)){
								jpk.append(key).append(",");
							}else if(cst.contains("JUK")){
								if(juk.containsKey(cst)){
									juk.get(cst).add(key);
								}else{
									List<String> tmpList = new ArrayList<String>();
									tmpList.add(key);
									juk.put(cst, tmpList);
								}
							}else if("UK".equals(cst)){
								uk.add(key);
							}
							
							tmpComments.append("COMMENT ON COLUMN ").append(tb.getTableNameEN()).append(".").append(key)
								.append(" IS '").append(tb.getTableColumnsCnMap().get(key))
								.append(preSpaceFormatter(tb.getTableCommentsMap().get(key).replaceAll("\n", ""))).append("';\n");
						}
						int delComma = tmpSQL.lastIndexOf(",");
						if(delComma > 0){
							tmpSQL.deleteCharAt(delComma);
							tmpSQL.append(");\n");
						}
						//主键约束不空
						if(StringUtils.isNotBlank(pk)){
							tmpConstraints.append("ALTER TABLE ").append(tb.getTableNameEN()).append("\n");
							tmpConstraints.append("ADD CONSTRAINT ").append("PK_").append(getSn()).append("\n");
							tmpConstraints.append("PRIMARY KEY (").append(pk).append(");\n");							
						}
						//唯一键不空
						if(!uk.isEmpty()){
							for(String s : uk){
								tmpConstraints.append("ALTER TABLE ").append(tb.getTableNameEN()).append("\n");
								tmpConstraints.append("ADD CONSTRAINT ").append("UK_").append(getSn()).append("\n");
								tmpConstraints.append("UNIQUE (").append(s).append(");\n");
							}
						}
						//外键不空
						if(!fk.isEmpty()){
							for(String s : fk){
								tmpConstraints.append("ALTER TABLE ").append(tb.getTableNameEN()).append("\n");
								tmpConstraints.append("ADD CONSTRAINT ").append("FK_").append(getSn()).append("\n");
								tmpConstraints.append("FOREIGN KEY(").append(s).append(")");
								tmpConstraints.append("REFERENCES ").append(tb.getTableColumnDefaultValueMap().get(s)).append(";\n");
							}
						}
						//联合主键不空
						if(StringUtils.isNotBlank(jpk)){
							tmpConstraints.append("ALTER TABLE ").append(tb.getTableNameEN()).append("\n");
							tmpConstraints.append("ADD CONSTRAINT ").append("JPK_").append(getSn()).append("\n");
							tmpConstraints.append("PRIMARY KEY (").append(jpk.deleteCharAt(jpk.lastIndexOf(","))).append(");\n");
						}
						//联合唯一不空
						if(!juk.isEmpty()){
							for(String s : juk.keySet()){
								tmpConstraints.append("ALTER TABLE ").append(tb.getTableNameEN()).append("\n");
								tmpConstraints.append("ADD CONSTRAINT ").append("JUK_").append(getSn()).append("\n");
								tmpConstraints.append("UNIQUE (");
								for(String col : juk.get(s)){
									tmpConstraints.append(col).append(",");
								}
								tmpConstraints = tmpConstraints.deleteCharAt(tmpConstraints.lastIndexOf(","));
								tmpConstraints.append(");\n");
							}
						}
						
						sql.append("--新增").append(tb.getTableNameEN()).append("表\n");
						sql.append(tmpSQL).append("\n");
						sql.append(tmpComments).append("\n");
						sql.append(tmpConstraints).append("\n");
						sql.append(tmpSeq).append("\n");
					//修改操作，检查备注后面的操作标注
					}else if("MD".equals(tb.getTableOpType())){
						//序列
						if(StringUtils.isNotBlank(tb.getTableSeq())){
							tmpSeq.append("CREATE SEQUENCE ").append(tb.getTableSeq()).append("\n");
							tmpSeq.append("MINVALUE 1").append("\n");
							tmpSeq.append("MAXVALUE 9999999999").append("\n");
							tmpSeq.append("START WITH 50").append("\n");
							tmpSeq.append("INCREMENT BY 1").append("\n");
							tmpSeq.append("CACHE 30;").append("\n");
						}
						//注解
						if(StringUtils.isNotBlank(tb.getTableDesc())){
							tmpComments.append("COMMENT ON TABLE ").append(tb.getTableNameEN()).append(" IS '").append(tb.getTableNameCN()).append("  ").append(preSpaceFormatter(tb.getTableDesc())).append("';\n");
						}

						//主键
						String pk = new String();
						//唯一键
						List<String> uk = new ArrayList<String>();
						//外键
						List<String> fk = new ArrayList<String>();
						//联合主键
						StringBuilder jpk = new StringBuilder();
						//联合唯一
						Map<String,List<String>> juk = new HashMap<String,List<String>>();
						List<String> add = new ArrayList<String>();
						List<String> del = new ArrayList<String>();
						List<String> md = new ArrayList<String>();
						for(String key : tb.getTableColumnsList()){
							if(StringUtils.isBlank(key)){
								continue;
							}
							String type = tb.getTableColumnTypeMap().get(key);
							if(StringUtils.isBlank(type)){
								continue;
							}
							String op = tb.getTableColumnOpTypeMap().get(key);
							//新增字段
							if("NEW".equals(op)){
								add.add(key);
							}else if("DEL".equals(op)){
								del.add(key);
								continue;
							}else if("MD".equals(op)){
								md.add(key);
							}
							
							
							String cst = tb.getTableConstraintsMap().get(key);
							if("PK".equals(cst)){
								if(StringUtils.isBlank(pk)){
									pk = key;
								}else{
									log.info("表中存在多个单一主键，忽略后续的主键");
									throw new BaseException(tb.getTableNameEN()+"表存在多个PK，请检查表定义！");
								}
							}else if("FK".equals(cst)){
							    fk.add(key);
							}else if("JPK".equals(cst)){
								jpk.append(key).append(",");
							}else if(cst.contains("JUK")){
								if(juk.containsKey(cst)){
									juk.get(cst).add(key);
								}else{
									List<String> tmpList = new ArrayList<String>();
									tmpList.add(key);
									juk.put(cst, tmpList);
								}
							}else if("UK".equals(cst)){
								uk.add(key);
							}
							
							tmpComments.append("COMMENT ON COLUMN ").append(tb.getTableNameEN()).append(".").append(key)
								.append(" IS '").append(tb.getTableColumnsCnMap().get(key)).append(" ")
								.append(preSpaceFormatter(tb.getTableCommentsMap().get(key).replaceAll("\n", ""))).append("';\n");
						}
						//新增字段不空
						if(!add.isEmpty()){
							tmpSQL.append("ALTER TABLE ").append(tb.getTableNameEN()).append(" ADD(\n");
							for(String key : add){
								String type = tb.getTableColumnTypeMap().get(key);
								String dfv = StringUtils.isBlank(tb.getTableColumnDefaultValueMap().get(key))?"":" DEFAULT "+tb.getTableColumnDefaultValueMap().get(key);
								tmpSQL.append(key).append(" ").append(type).append(dfv).append(getNullOrNotStr(tb.getTableConstraintsMap().get(key))).append(",");
							}
							tmpSQL.deleteCharAt(tmpSQL.lastIndexOf(","));
							tmpSQL.append(");\n");
						}
						//删除的列不为空
						if(!del.isEmpty()){
							tmpSQL.append("ALTER TABLE ").append(tb.getTableNameEN()).append(" DROP(\n");
							for(String key : del){
								tmpSQL.append(key).append(",");
							}
							tmpSQL = tmpSQL.deleteCharAt(tmpSQL.lastIndexOf(","));
							tmpSQL.append(");\n");
						}
						//修改的列不为空
						if(!md.isEmpty()){
							tmpSQL.append("ALTER TABLE ").append(tb.getTableNameEN()).append(" MODIFY(\n");
							for(String key : md){
								String type = tb.getTableColumnTypeMap().get(key);
								String dfv = StringUtils.isBlank(tb.getTableColumnDefaultValueMap().get(key))?" ":"DEFAULT "+tb.getTableColumnDefaultValueMap().get(key);
								tmpSQL.append(key).append(" ").append(type).append(dfv).append(getNullOrNotStr(tb.getTableConstraintsMap().get(key))).append(",");
							}
							tmpSQL = tmpSQL.deleteCharAt(tmpSQL.lastIndexOf(","));						
							tmpSQL.append(");\n");
						}
						//主键约束不空
						if(StringUtils.isNotBlank(pk)){
							tmpConstraints.append("ALTER TABLE ").append(tb.getTableNameEN()).append("\n");
							tmpConstraints.append("ADD CONSTRAINT ").append("PK_").append(getSn()).append("\n");
							tmpConstraints.append("PRIMARY KEY (").append(pk).append(");\n");							
						}
						//唯一键不空
						if(!uk.isEmpty()){
							for(String s : uk){
								tmpConstraints.append("ALTER TABLE ").append(tb.getTableNameEN()).append("\n");
								tmpConstraints.append("ADD CONSTRAINT ").append("UK_").append(getSn()).append("\n");
								tmpConstraints.append("UNIQUE (").append(s).append(");\n");
							}
						}
						//外键不空
						if(!fk.isEmpty()){
							for(String s : fk){
								tmpConstraints.append("ALTER TABLE ").append(tb.getTableNameEN()).append("\n");
								tmpConstraints.append("ADD CONSTRAINT ").append("FK_").append(getSn()).append("\n");
								tmpConstraints.append("FOREIGN KEY(").append(s).append(")");
								tmpConstraints.append("REFERENCES ").append(tb.getTableColumnDefaultValueMap().get(s)).append(";\n");
							}
						}
						//联合主键不空
						if(StringUtils.isNotBlank(jpk)){
							tmpConstraints.append("ALTER TABLE ").append(tb.getTableNameEN()).append("\n");
							tmpConstraints.append("ADD CONSTRAINT ").append("JPK_").append(getSn()).append("\n");
							tmpConstraints.append("PRIMARY KEY (").append(jpk.deleteCharAt(jpk.lastIndexOf(","))).append(");\n");
						}
						//联合唯一不空
						if(!juk.isEmpty()){
							for(String s : juk.keySet()){
								tmpConstraints.append("ALTER TABLE ").append(tb.getTableNameEN()).append("\n");
								tmpConstraints.append("ADD CONSTRAINT ").append("JUK_").append(getSn()).append("\n");
								tmpConstraints.append("UNIQUE (");
								for(String col : juk.get(s)){
									tmpConstraints.append(col).append(",");
								}
								tmpConstraints = tmpConstraints.deleteCharAt(tmpConstraints.lastIndexOf(","));
								tmpConstraints.append(");\n");
							}
						}

						sql.append("--修改").append(tb.getTableNameEN()).append("表\n");
						sql.append(tmpSQL).append("\n");
						sql.append(tmpComments).append("\n");
						sql.append(tmpConstraints).append("\n");
						sql.append(tmpSeq).append("\n");
					
					//删除操作,忽略备注后面的操作标注，会删除序列，如果不空
					}else if("DEL".equals(tb.getTableOpType())){
						if(StringUtils.isNotBlank(tb.getTableNameEN())){
							//删除表
							tmpSQL.append("DROP TABLE ").append(tb.getTableNameEN()).append(";'\n");							
						}
						if(StringUtils.isNotBlank(tb.getTableSeq())){
							//删除序列
							tmpSeq.append("DROP SEQUENCE ").append(tb.getTableSeq()).append(";\n");
						}
						sql.append("--删除").append(tb.getTableNameEN()).append("表\n");
						sql.append(tmpSQL).append("\n");

					}else{
						throw new BaseException();
					}
				} catch (BaseException e) {
					log.info(e.getMessage());
					sql.append("\n\n--"+e.getMessage()+"\n\n");
				}

			}
		} catch (Exception e) {
			log.error("翻译为ORACLE异常！",e);
			sql = new StringBuilder("系统异常！");
		}
		return sql.toString();
	}
	
	/**
	 * 获取一个时间序列，为了不重复，该方法是同步方法
	 * @return
	 * @throws Exception
	 */
	private synchronized String getSn() throws Exception{
		Thread.sleep(10);
		return CONST_NAME.format(new Date());
	}
	
	/**
	 * 根据给定字符是否空，前缀空格
	 * @param cnt
	 * @return
	 */
	private String preSpaceFormatter(String cnt){
		try {
			if(StringUtils.isBlank(cnt)){
				return "";
			}else{
				return " "+cnt;
			}
		} catch (Exception e) {
			log.error("preSpaceFormatter异常！",e);
			return "";
		}
	}
	
	/**
	 * 根据给定的关键字
	 * 返回SQL的非空约束
	 * @param key
	 * @return
	 */
	private String getNullOrNotStr(String key){
		try {
			switch(key){
				case "NULL":
					return "";
				case "NOT":
					return " NOT NULL";
				default:
					return "";
			}
		} catch (Exception e) {
			log.error(e);
			return "";
		}
	}

}
